##############################################################################
#
# Zope Public License (ZPL) Version 1.0
# -------------------------------------
#
# Copyright (c) Digital Creations.  All rights reserved.
#
# This license has been certified as Open Source(tm).
#
# Redistribution and use in source and binary forms, with or without
# modification, are permitted provided that the following conditions are
# met:
#
# 1. Redistributions in source code must retain the above copyright
#    notice, this list of conditions, and the following disclaimer.
#
# 2. Redistributions in binary form must reproduce the above copyright
#    notice, this list of conditions, and the following disclaimer in
#    the documentation and/or other materials provided with the
#    distribution.
#
# 3. Digital Creations requests that attribution be given to Zope
#    in any manner possible. Zope includes a "Powered by Zope"
#    button that is installed by default. While it is not a license
#    violation to remove this button, it is requested that the
#    attribution remain. A significant investment has been put
#    into Zope, and this effort will continue if the Zope community
#    continues to grow. This is one way to assure that growth.
#
# 4. All advertising materials and documentation mentioning
#    features derived from or use of this software must display
#    the following acknowledgement:
#
#      "This product includes software developed by Digital Creations
#      for use in the Z Object Publishing Environment
#      (http://www.zope.org/)."
#
#    In the event that the product being advertised includes an
#    intact Zope distribution (with copyright and license included)
#    then this clause is waived.
#
# 5. Names associated with Zope or Digital Creations must not be used to
#    endorse or promote products derived from this software without
#    prior written permission from Digital Creations.
#
# 6. Modified redistributions of any form whatsoever must retain
#    the following acknowledgment:
#
#      "This product includes software developed by Digital Creations
#      for use in the Z Object Publishing Environment
#      (http://www.zope.org/)."
#
#    Intact (re-)distributions of any official Zope release do not
#    require an external acknowledgement.
#
# 7. Modifications are encouraged but must be packaged separately as
#    patches to official Zope releases.  Distributions that do not
#    clearly separate the patches from the original work must be clearly
#    labeled as unofficial distributions.  Modifications which do not
#    carry the name Zope may be packaged in any form, as long as they
#    conform to all of the clauses above.
#
#
# Disclaimer
#
#   THIS SOFTWARE IS PROVIDED BY DIGITAL CREATIONS ``AS IS'' AND ANY
#   EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
#   IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
#   PURPOSE ARE DISCLAIMED.  IN NO EVENT SHALL DIGITAL CREATIONS OR ITS
#   CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
#   SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
#   LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
#   USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
#   ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
#   OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
#   OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
#   SUCH DAMAGE.
#
#
# This software consists of contributions made by Digital Creations and
# many individuals on behalf of Digital Creations.  Specific
# attributions are listed in the accompanying credits file.
#
##############################################################################

'''$Id: db.py 16 2006-11-24 14:28:11Z pigletto $'''
__version__='$Revision: 1.13 $'[11:-2]

import DCOracle2, DateTime
#DCOracle.dbi.dbiDate=DateTime.DateTime
from Shared.DC.ZRDB.TM import TM

import string, sys
from string import strip, split, find
from time import time

import transaction
from Shared.DC.ZRDB.TM import Surrogate
from ZPublisher import Retry
from ZODB.POSException import ConflictError

from DCOracle2 import InterfaceError
from DCOracle2 import dco2
import pool
import logging
logger = logging.getLogger('DCOracle2 - db.py')

failures=0
calls=0
last_call_time=time()

IMPLICITLOBS=1      # Turn this off to get old LOB behavior

# List of Oracle errors associated with database connection break
ORA_CONNECTION_ERRORS = (3114, # ORA-03114: not connected to ORACLE
                         1012, # ORA-01012: not logged on
                         3113, # ORA-03113: end-of-file on communication channel
                         #ORA-12505 ?
                         #28, 'ORA-00028: sesja zostala zlikwidowana'
                        )

class DB(TM):

    _p_oid=_p_changed=_registered=None

   # Database_Connection=DCOracle2.Connect
    Database_Error=DCOracle2.DatabaseError

    def __init__(self, connection_string, unique_id):
        self.connection_string=connection_string
        self.unique_id = unique_id
        self.db = DCOracle2.connect(connection_string)        
        logger.debug('__init__ - created: \'%s\'' % (connection_string))

    def _register(self):
        if not self._registered:
            try:
                logger.debug('registered transaction for rm: %s' % (self.unique_id))
                transaction.get().register(Surrogate(self))
                self._begin()
                self._registered = 1
                self._finalize = 0
            except: pass

    def str(self,v, StringType=type('')):
        if v is None: return ''
        r=str(v)
        if r[-1:]=='L' and type(v) is not StringType: r=r[:-1]
        return r

    def getcursor(self):
        return self.db.cursor()

    def close(self):
        logger.debug('close')
        if self.isOpen():
            self.db.close()
            self.db = None
        
    def __del__(self):
        self.db = None

    def registerRM(self):
        self._register()

    def _finish(self, *ignored):
        logger.debug('_finish')
        try:
            self.db.commit()
            pool.putconn(self.connection_string, self.unique_id, False)
        except AttributeError, err:
            pass
        except dco2.DatabaseError, e:
            logger.error( '_finish - database error: %s' % (e), exc_info=True )
            # Something happened that we were unable to commit
            # if it was problem with db connection we will close it and try to
            # repeat the request, otherwise raise original error
            # Connection will be automatically reopened during next request 
            # because of code in DABase.py -> getconn
            if e.args[0] in ORA_CONNECTION_ERRORS:
                logger.error( \
                  '_finish - closing db connection and raising ConflictError',
                  exc_info=True )
                self.db.close()                
                # retry the whole request
                #raise Retry(*sys.exc_info())  #ConflictError
                raise ConflictError
            raise
        except InterfaceError:
            logger.error('_abort InterfaceError', exc_info=True)

    def _abort(self, *ignored):
        logger.debug('_abort')
        if not self.isOpen():
            logger.debug('_abort, not isOpen')
            pool.putconn(self.connection_string, self.unique_id, True)
            return 0
        try:
            self.db.rollback()
            logger.debug('_abort - rollback called')
        except AttributeError, err:
            logger.debug('_abort, attr error po rollback', exc_info=True)
            pass
        except dco2.DatabaseError, e:
            logger.error( '_abort - database error: %s' % (e), exc_info=True )
            # Something happened that we were unable to abort
            # if it was problem with db connection we will close it and try to
            # repeat the request, otherwise raise original error
            # Connection will be automatically reopened during next request 
            # because of code in DABase.py -> getconn
            if e.args[0] in ORA_CONNECTION_ERRORS:
                logger.error( \
                  '_abort - closing db connection and raising ConflictError',
                  exc_info=True )
                self.db.close()                
                # retry the whole request
                #raise Retry(*sys.exc_info())  #ConflictError
                raise ConflictError
            raise
        except InterfaceError:
            logger.error('_abort InterfaceError', exc_info=True)
        pool.putconn(self.connection_string, self.unique_id, False)

    def isOpen(self):
        """ Check whether db connection is opened
        """
        return self.db is not None and self.db.isOpen()

    def tables(self, rdb=0,
               _care=('TABLE', 'VIEW')):
        r=[]
        a=r.append
        for name, typ in self.db.objects():
            if typ in _care:
                a({'TABLE_NAME': name, 'TABLE_TYPE': typ})
        return r


    def columns(self, table_name):
        c=self.getcursor()
        try: r=c.execute('select * from %s' % table_name)
        except: return ()
        desc=c.description
        r=[]
        a=r.append
        for name, type, width, ds, p, scale, null_ok in desc:
            if type=='NUMBER' and scale==0: type='INTEGER'
            if type == "VARCHAR" or type == "CHAR" or type == "VARCHAR2" or type == "STRING":
                p= width
            if null_ok:
                null_ok = "with NULL"
            else:
                null_ok = ""
            if type == 'INTEGER' and p == 38: p = 0  # default precision
            a({ 'Name': name,
                'Type': type,
                'Precision': p,
                'Scale': scale,
                'Nullable': null_ok,
                })

        return r

    def query(self,query_string, max_rows=9999999):
        logger.debug('query')
        global failures, calls, last_call_time
        calls=calls+1
        desc=None
        result=()
        if not self._registered:
            self.qq = query_string.replace('\n', '')
        self._register()
        c=self.getcursor()
        try:
            for qs in filter(None, map(strip,split(query_string, '\0'))):
                r=c.execute(qs) # Returns 1 on SELECT

                if desc is not None:
                    if c.description != desc:
                        raise 'Query Error', (
                            'Multiple select schema are not allowed'
                            )
                    if type(result) is not ListType: result=list(result)
                    if r == 1 and max_rows:
                        for row in c.fetchmany(max_rows-len(result)):
                            result.append(row)
                else:
                    desc=c.description
                    if r == 1 and max_rows:
                        if max_rows==1: result=(c.fetchone(),)
                        else: result=c.fetchmany(max_rows)

            failures=0
            last_call_time=time()        
        except self.Database_Error, e:
            # if it looks like an error caused by a hosed connection
            # then stomp on it and then use ZPublisher's Retry
            # functionality.
            # This will retry the request up to 3 times for HTTP requests
            # if it still fails then, well, we should stop retrying ;-)
            if e.args[0] in ORA_CONNECTION_ERRORS:
                # Because of error we are clossing broken connection
                # Connection will be automatically reopened during next request 
                # because of code in DABase.py -> getconn
                logger.error('query: Closing connection because of ORA error', exc_info=True)
                self.db.close()
                logger.debug('query: raising conflicterror')
                # retry the whole request
                raise ConflictError  #Retry(*sys.exc_info())
                #raise Retry(*sys.exc_info())  #ConflictError

            # otherwise, just raise it so we can see what's going on
            raise        

        if desc is None: return (),()

        items=[]
        for name, type, width, ds, p, scale, null_ok in desc:
            if type=='NUMBER':
                if scale==0: type='i'
                else: type='n'
            elif type=='DATE':
                type='d'
            else: type='s'
            items.append({
                'name': name,
                'type': type,
                'width': width,
                'null': null_ok,
                })

        #
        # Loop over all result values, changing them to LobLocator class
        # if they are DCOracle2.dco2.LobLocators
        #
        self._lobConvert(result)
        return items, result

    #
    # Do we get tuples back in results? should just be lists
    #   
    def _lobConvert(self, result, listtype=type([])):
        # Mutates result in place, but also returns mutated rsult
        unconvertlist = 0
        if type(result) is not listtype:
            result = [result,]
            unconvertlist = 1
        for i in xrange(len(result)):
            t = type(result[i])
            if t == listtype: self._lobConvert(result[i])
            elif t == DCOracle2.dco2.LobLocatorType:
                result[i] = LobLocator(result[i])
            elif t == DCOracle2.dco2.OracleDateType:
                # DateTime supports dates later than 1901.01.01
                # so we're checking them 
                # conversion is done if necessary
                # and error message is written to logs
                dt = str(result[i])
                try:           
                    dt_list = dt.split('-')
                    year = int(dt_list[0])
                    if year<1901:
                        logger.warning('Date: \'%s\' is not supported! Year \
 changed to lowest possible: 1901' % (dt))
                        dt_list[0] = '1901'
                        dt = '-'.join(dt_list)
                    result[i] = DateTime.DateTime(dt)
                except DateTime.DateTime.SyntaxError, err:                    
                    logger.warning('Error: \'%s\'. Date: \'%s\' is not \
 supported! Changed to lowest possible: 1901.01.01' % (err, dt))
                    result[i] = DateTime.DateTime('1901-01-01 00:00:00')
        if unconvertlist: result = result[0]
        return result

    # Added for ChrisM (11/13/2000 MTK)
    def commit_sub(self, *arg, **kw):
        pass

    # Added for ChrisM (11/13/2000 MTK)
    def abort_sub(self, *arg, **kw):
        pass

    # proxy methods to self.db necessary for SP.py
    # with pool implementation
    def collapsedesc(self, *args, **kw):
        """ Proxy method to collapsedesc of self.db
        """
        self._register()
        return self.db.collapsedesc(*args, **kw)

    def objects(self, *args, **kw):
        """ Proxy method to objects od self.db
        """
        self._register()
        return self.db.objects(*args, **kw)

    def describe(self, *args, **kw):
        """ Proxy method to describe of self.db
        """
        self._register()
        return self.db.describe(*args, **kw)

#
# The LobLocator class is a workaround for the Zope security manager;
# the alternative would have been to put a getattr for the hack into
# the C bindings.
#

class LobLocator:
    __allow_access_to_unprotected_subobjects__ = 1

    #__ac_permissions__ = (
    #    ('Use LOB objects',     ('','read','write','trim','length')),
    #    )

    def __init__(self, lob):
        self.lob = lob
        self.read = lob.read
        self.write = lob.write
        self.trim = lob.trim
        self.length = lob.length

    if IMPLICITLOBS:

        def __str__(self):
            return self.read()

        def __repr__(self):
            return "<LobLocator at 0x%08x>" % id(self.lob)

        def __len__(self):
            return self.length()

